package cake.dao.impl;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cake.dao.CommentDao;
import cake.entity.Comment;



public class CommentDaoImpl implements CommentDao{
	
	private JdbcTemplate db= new JdbcTemplate();
	
	private static class CommentRowMapper implements RowMapper<Comment>{
		public Comment mapRow(ResultSet rs) throws SQLException {
			return new Comment(rs.getInt("id"),rs.getInt("uid"),rs.getInt("cid"),rs.getInt("score"),rs.getString("content"),rs.getDate("date"));
		}
	}
	private static class CommentAndUserRowMapper implements RowMapper<Comment>{
		public Comment mapRow(ResultSet rs) throws SQLException {
			return new Comment(rs.getInt("id"),rs.getInt("uid"),rs.getInt("cid"),rs.getInt("score"),rs.getString("content"),rs.getDate("date"),rs.getString("username"));
		}
	}
	private static class CommentAndUserAndCakeRowMapper implements RowMapper<Comment>{
		public Comment mapRow(ResultSet rs) throws SQLException {
			return new Comment(rs.getInt("id"),rs.getInt("uid"),rs.getInt("cid"),rs.getInt("score"),rs.getString("content"),rs.getDate("date"),rs.getString("username"),rs.getString("name"));
		}
	}
	
	
	public List<Comment> getAll() {
		String sql = "select * from comment";
		return db.query(sql, new CommentRowMapper());
	}

	@Override
	public List<Comment> getThree(int id,int pageSize, int pageNum) {
		String sql = "select * from comment where cid=? limit ?,?";
		return db.query(sql, new CommentRowMapper(),id,(pageNum-1)*pageSize,pageSize);
	}
	
	public List<Comment> getThreeAndUser(int id,int pageSize, int pageNum) {
		String sql = "select c.*,u.username from comment c left join user u on u.id=c.uid where cid=? limit ?,?";
		return db.query(sql, new CommentAndUserRowMapper(),id,(pageNum-1)*pageSize,pageSize);
	}
	
	public List<Comment> getUserAndCake(int cid,String title,int pageSize, int pageNum){
		ArrayList parameters = new ArrayList();
		String sql = "select cm.*,u.username,c.name from comment cm left join user u on u.id=cm.uid "+ 
						"left join cake c on c.id = cm.cid where 1=1";
		if(cid>0) {
			sql+= " and cid = ?";
			parameters.add(cid);
		}
		if(title!=null && title!="") {
			sql += " and c.name like ?";
			parameters.add("%"+title+"%");
		}
		sql += " limit ?,?";
		parameters.add((pageNum-1)*pageSize);
		parameters.add(pageSize);
		return db.query(sql, new CommentAndUserAndCakeRowMapper(),parameters.toArray());
	}
	@Override
	public boolean addComment(Comment c) {
		String sql = "INSERT INTO  COMMENT(uid,cid,score,content,DATE) VALUES (?,?,?,?,?)";
		return db.update(sql, c.getUid(),c.getCid(),c.getScore(),c.getContent(),c.getDate());
	}

	@Override
	public boolean deleteComment(int id) {
		String sql = "delete from comment where id = ?";
		return db.update(sql, id);
	}
	
	public int fetchCommentRows(int cid) {
		String sql = "select count(id) from comment where cid=?";
		return db.queryOne(sql,new RowMapper<Integer>() {
			public Integer mapRow(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		},cid);
	}
	public int fetchCommentRowsUC(int cid,String title) {
		ArrayList parameters = new ArrayList();
		String sql = "SELECT COUNT(cm.id) FROM COMMENT cm LEFT JOIN USER u ON u.id=cm.uid "+ 
						"LEFT JOIN cake c ON c.id = cm.cid WHERE 1=1";
		if(cid>0) {
			sql+= " and cid = ?";
			parameters.add(cid);
		}
		if(title!=null && title!="") {
			sql += " and c.name like ?";
			parameters.add("%"+title+"%");
		}
		return db.queryOne(sql,new RowMapper<Integer>() {
			public Integer mapRow(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		},parameters.toArray());
	}
	
	public static void main(String[] args) {
		CommentDaoImpl commentDao = new CommentDaoImpl();
//		System.out.println(commentDao.fetchCommentRowsUC(2, ""));
//		System.out.println(commentDao.fetchCommentRows(1));
//		List<Comment> list = commentDao.getUserAndCake();
//		for(Comment c:list) {
//			System.out.println(c.getName());
//		}
//		Comment c = new Comment(0,3,1,5,"好吃",new Date(0));
//		System.out.println(commentDao.addComment(c));
//   	System.out.println(commentDao.deleteComment(21));
	}
}
